import json
from collections import defaultdict, Counter

from wordcloud import WordCloud

from utils.query import query

cityList = [
    {
        "province": "安徽",
        "city": ["安庆", "滁州", "阜阳", "合肥", "六安", "马鞍山", "芜湖"],
    },
    {"province": "北京", "city": ["北京"]},
    {"province": "重庆", "city": ["重庆"]},
    {"province": "福建", "city": ["福州", "泉州", "厦门", "漳州"]},
    {
        "province": "广东",
        "city": [
            "东莞",
            "佛山",
            "广州",
            "惠州",
            "江门",
            "清远",
            "深圳",
            "珠海",
            "湛江",
            "中山",
            "贵阳",
            "黔西南",
            "北海",
            "防城港",
            "桂林",
            "柳州",
            "南宁",
            "兰州",
            "天水",
        ],
    },
    {
        "province": "湖北",
        "city": [
            "鄂州",
            "黄石",
            "黄冈",
            "武汉",
            "襄阳",
            "宜昌",
            "长沙",
            "常德",
            "衡阳",
            "湘西",
            "岳阳",
            "株洲",
            "保定",
            "承德",
            "邯郸",
            "廊坊",
            "秦皇岛",
            "石家庄",
            "唐山",
            "张家口",
            "保亭",
            "澄迈",
            "儋州",
            "海口",
            "临高",
            "乐东",
            "陵水",
            "琼海",
            "三亚",
            "五指山",
            "文昌",
            "万宁",
            "济源",
            "开封",
            "洛阳",
            "平顶山",
            "濮阳",
            "三门峡",
            "新乡",
            "许昌",
            "郑州",
            "周口",
            "驻马店",
            "哈尔滨",
        ],
    },
    {
        "province": "江苏",
        "city": [
            "常州",
            "常熟",
            "丹阳",
            "海门",
            "淮安",
            "江阴",
            "句容",
            "昆山",
            "南京",
            "南通",
            "苏州",
            "太仓",
            "无锡",
            "徐州",
            "盐城",
            "镇江",
            "长春",
            "吉林",
            "赣州",
            "九江",
            "吉安",
            "南昌",
            "上饶",
        ],
    },
    {"province": "辽宁", "city": ["大连", "丹东", "抚顺", "沈阳"]},
    {
        "province": "内蒙古",
        "city": ["包头", "巴彦淖尔", "赤峰", "呼和浩特", "通辽", "银川"],
    },
    {
        "province": "上海",
        "city": [
            "上海",
            "成都",
            "德阳",
            "达州",
            "广元",
            "乐山",
            "凉山",
            "绵阳",
            "眉山",
            "南充",
            "攀枝花",
            "遂宁",
            "宜宾",
            "雅安",
            "资阳",
            "菏泽",
            "济南",
            "济宁",
            "临沂",
            "青岛",
            "泰安",
            "潍坊",
            "威海",
            "烟台",
            "淄博",
            "宝鸡",
            "汉中",
            "西安",
            "咸阳",
            "晋中",
            "太原",
            "运城",
        ],
    },
    {"province": "天津", "city": ["天津"]},
    {"province": "新疆", "city": ["乌鲁木齐"]},
    {"province": "云南", "city": ["大理", "昆明", "西双版纳"]},
    {
        "province": "浙江",
        "city": [
            "杭州",
            "湖州",
            "嘉兴",
            "金华",
            "宁波",
            "衢州",
            "绍兴",
            "台州",
            "温州",
            "义乌",
        ],
    },
]


def queryHouseDataField(field, limit=500):
    """
    查询指定字段的前limit条数据，默认limit=500
    :param field:
    :param limit:
    :return:
    """
    data = query(f"select {field} from house_info group by {field} limit {limit}")
    res = []
    for row in data:
        if len(row) == 1:
            res.append(row[0])
        else:
            res.append(row)
    return res


def queryHouseDataFieldCnt(field, cntField, where="id > 0"):
    """
    查询指定字段的数量，并按照cntField排序
    :param field: 分组字段
    :param cntField: 统计字段
    :param where: 查询条件默认可以忽略
    :return:
    """
    data = query(
        f"select {field},count({cntField}) from house_info where {where} group by {field}"
    )
    res = []
    for row in data:
        res.append(row)
    return res


def queryGroupByLimit(field, limit=10):
    """
    查询各个分组字段的TopN数据
    :param field:
    :param limit:
    :return:
    """
    sql = f"""
	SELECT {field}
	FROM (SELECT {field},
	             ROW_NUMBER() OVER (PARTITION BY city ORDER BY id) as row_num
	      FROM house_info) ranked
	WHERE row_num <= {limit};
	"""
    data = query(sql)
    res = []
    for row in data:
        if len(row) == 1:
            res.append(row[0])
        else:
            res.append(row)
    return res


def queryCityData():
    """
    查询各个省份的房屋价格和房屋数量
    :return:
    """
    # 城市，价格信息 =》 省份，价格信息
    data = query("select city,prices from house_info")
    city_price_dict = defaultdict(int)
    city_cnt = Counter()
    for city, price in data:
        city_price_dict[city] += int(price)
        city_cnt[city] += 1

    province_price_dict = defaultdict(int)
    province_cnt = Counter()
    for province_city_dict in cityList:
        province = province_city_dict["province"]
        citys = province_city_dict["city"]

        for city in citys:
            for city_, price in city_price_dict.items():
                # 查询城市对应的省份
                if city_ == city:
                    province_price_dict[province] += price
                    province_cnt[province] += city_cnt[city_]

    return province_price_dict, province_cnt


def getHouseProvinceAvgPriceChinaMapData():
    """
    查询各省份的平均房价
    :return:
    """
    province_price_dict, province_cnt = queryCityData()
    average_price_dict = {}
    for province, price in province_price_dict.items():
        if province_cnt[province] > 0:
            average_price_dict[province] = round(price / province_cnt[province], 2)
    return average_price_dict


def getHouseTagsDta():
    """
    获取房屋数量，获取房屋最大价格，获取出现次数最多的房屋类型，和房屋售卖状态
    :return:
    """
    size = query("select count(*) from house_info")[0][0]
    max_price = query("select max(prices) from house_info")[0][0]
    house_type, house_type_max = query(
        """select house_type,count(*) cnt
			from house_info
			group by house_type
			order by count(*) desc limit 1
		"""
    )[0]
    status_type, status_type_max = query(
        """select sale_status,count(*) cnt
			from house_info
			group by sale_status
			order by count(*) desc limit 1
		"""
    )[0]
    return {
        "size": size,
        "max_price": max_price,
        "house_type": house_type,
        "house_type_max": house_type_max,
        "status_type": status_type,
        "status_type_max": status_type_max,
    }


def getHouseRadarData():
    """
    获取各城市房屋数量
    :return:
    """
    city_cnt = query(
        "select city,count(*) cnt from house_info group by city order by cnt desc limit 10"
    )
    return city_cnt


def getHouseWordCloud():
    """
    制作房屋标签词云图
    :return:
    """
    word_cnt = Counter()
    tmp = query("select tags from house_info")
    for tags in tmp:
        for tag in json.loads(tags[0]):
            word_cnt[tag] += 1
    wc = WordCloud(
        font_path="STHUPO.TTF", background_color="white", width=266, height=354
    )
    wc.generate_from_frequencies(dict(word_cnt.most_common(50)))
    wc.to_file("../static/image/wordcloud.png")


def echartsData(data):
    """
    将数据转换为echarts可用的格式
    :param data:
    :return:
    """
    # [{"name": "广东", "value": 10000}, {"name": "北京", "value": 20000},{}]
    echarts_data = []
    if isinstance(data, dict):
        for key, val in data.items():
            if key is None or key == "":
                continue
            echarts_data.append({"name": key, "value": val})
        return json.dumps(echarts_data, ensure_ascii=False)
    elif isinstance(data, list) or isinstance(data, tuple):
        for key, val in data:
            if key is None or key == "":
                continue
            echarts_data.append({"name": key, "value": val})
        return json.dumps(echarts_data, ensure_ascii=False)


def searchData(keyword, field="*", limit=500):
    """
    根据搜索关键词搜索房屋信息
    :param keyword:
    :param field:
    :param limit:
    :return:
    """
    data = query(
        f"select distinct {field} from house_info where title like '%{keyword}%' limit {limit}"
    )
    res = []
    for row in data:
        if len(row) == 1:
            res.append(row[0])
        else:
            res.append(row)
    return res


if __name__ == "__main__":
    print(echartsData(queryHouseDataFieldCnt("house_decoration", "*", "city=保定")))
    pass
